//
//  DatabaseManager.m
//  数据库
//
//  Created by dllo on 15/10/8.
//  Copyright © 2015年 LuckyHan. All rights reserved.
//

#import "DatabaseManager.h"
#import "Record.h"

static sqlite3 *dbPoint = nil;

@implementation DatabaseManager

#pragma mark 打开数据库
+ (void)openDatabase
{
    if (dbPoint) {
        return;
    }
    NSString *path = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES).lastObject;
    path = [path stringByAppendingPathComponent:@"MyDatabase.sqlite"];
    sqlite3_open(path.UTF8String, &dbPoint);
    
//    NSLog(@"%@", path);
}

#pragma mark 关闭数据库
+ (void)closeDatabase
{
    int result = sqlite3_close(dbPoint);
    if (result == SQLITE_OK) {
//        NSLog(@"关闭数据库成功");
        dbPoint = nil;
    } else {
//        NSLog(@"关闭数据库失败: %d", result);
    }
}

#pragma mark 创建表格
+ (void)createTable
{
    [self openDatabase];
    NSString *sqlString = @"CREATE TABLE IF NOT EXISTS MyTable(contentId varchar(50) PRIMARY KEY, itemName varchar(20), title varchar(200))";
    int result = sqlite3_exec(dbPoint, sqlString.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
//        NSLog(@"表格创建成功");
    } else {
//        NSLog(@"表格创建失败: %d", result);
    }
    [self closeDatabase];
}

#pragma mark 插入记录
+ (void)insertRecord:(Record *)record
{
    [self openDatabase];
    NSString *sqlString = [NSString stringWithFormat:@"INSERT INTO MyTable(contentId, itemName, title) VALUES('%@', '%@', '%@')", record.contentId, record.itemName, record.title];
    int result = sqlite3_exec(dbPoint, sqlString.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
//        NSLog(@"插入成功");
    } else {
//        NSLog(@"插入失败: %d", result);
    }
    [self closeDatabase];
}

#pragma mark 删除记录
+ (void)deleteRecord:(NSString *)contentId
{
    [self openDatabase];
    NSString *sqlString = [NSString stringWithFormat:@"DELETE FROM MyTable WHERE contentId = '%@'", contentId];
    int result = sqlite3_exec(dbPoint, sqlString.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
//        NSLog(@"删除成功");
    } else {
//        NSLog(@"删除失败: %d", result);
    }
    [self closeDatabase];
}

#pragma mark 更新记录
+ (void)updateRecord:(Record *)record ContentId:(NSString *)contentId
{
    [self openDatabase];
    NSString *sqlString = [NSString stringWithFormat:@"UPDATE MyTable SET contentId = '%@', itemName = '%@', title = '%@' WHERE contentId = '%@'", record.contentId, record.itemName, record.title, contentId];
    int result = sqlite3_exec(dbPoint, sqlString.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
//        NSLog(@"更新成功");
    } else {
//        NSLog(@"更新失败: %d", result);
    }
    [self closeDatabase];
}

#pragma mark 数据库查询
+ (NSMutableArray *)selectRecords
{
    [self openDatabase];
    sqlite3_stmt *stmt = nil;
    NSString *sqlString = @"SELECT * FROM MyTable";
    int result = sqlite3_prepare_v2(dbPoint, sqlString.UTF8String, -1, &stmt, nil);
    NSMutableArray *selectArray = [NSMutableArray array];
    if (result == SQLITE_OK) {
//        NSLog(@"查询成功");
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            const unsigned char *contentId = sqlite3_column_text(stmt, 0);
            const unsigned char *itemName = sqlite3_column_text(stmt, 1);
            const unsigned char *title = sqlite3_column_text(stmt, 2);
            NSString *contentId_ = [NSString stringWithUTF8String:(const char *)contentId];
            NSString *itemName_ = [NSString stringWithUTF8String:(const char *)itemName];
            NSString *title_ = [NSString stringWithUTF8String:(const char *)title];
            Record *record = [[Record alloc] init];
            record.contentId = contentId_;
            record.itemName = itemName_;
            record.title = title_;
            [selectArray addObject:record];
            [record release];
        }
    } else {
//        NSLog(@"查询失败: %d", result);
    }
    sqlite3_finalize(stmt);
    [self closeDatabase];
    return selectArray;
}

#pragma mark 模糊查询
+ (NSMutableArray *)selectRecordsBySearchText:(NSString *)searchText
{
    [self openDatabase];
    sqlite3_stmt *stmt = nil;
    NSString *sqlString = [NSString stringWithFormat:@"SELECT * FROM MyTable WHERE title LIKE '%%%@%%'", searchText];
    int result = sqlite3_prepare_v2(dbPoint, sqlString.UTF8String, -1, &stmt, nil);
    NSMutableArray *selectArray = [NSMutableArray array];
    if (result == SQLITE_OK) {
//        NSLog(@"查询成功");
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            const unsigned char *contentId = sqlite3_column_text(stmt, 0);
            const unsigned char *itemName = sqlite3_column_text(stmt, 1);
            const unsigned char *title = sqlite3_column_text(stmt, 2);
            NSString *contentId_ = [NSString stringWithUTF8String:(const char *)contentId];
            NSString *itemName_ = [NSString stringWithUTF8String:(const char *)itemName];
            NSString *title_ = [NSString stringWithUTF8String:(const char *)title];
            Record *record = [[Record alloc] init];
            record.contentId = contentId_;
            record.itemName = itemName_;
            record.title = title_;
            [selectArray addObject:record];
            [record release];
        }
    } else {
//        NSLog(@"查询失败: %d", result);
    }
    sqlite3_finalize(stmt);
    [self closeDatabase];
    return selectArray;
}

@end
